Aggregate Functions, Conservative Extension, and Linear Orders and Then Taking Their Big Union. Hence

1 Summary Practical database query languages are usually equipped with some aggregate functions. For example, \\nd mean of column" can be expressed in SQL. However, the manner in which aggregate functions were introduced in these query languages leaves something to be desired. Breazu-Tannen, Buneman, and Wong 3] introduced a nested relational language NRC(=) based on monads 16, 24] and structural recursion 1, 2]. It was shown in Wong 27] that this language is equivalent to the nested relational algebras of Thomas and Fischer 22], Schek and Scholl 20], and Colby 4]. NRC(=) enjoys certain advantages over these languages: it is naturally embedded in functional languages, it is readily extensible, and it has a compact equational theory. Therefore, it is used in this report as a basis for investigating aggregate functions. In section 2, the nested relational calculus NRC(=) is described. It is then endowed with rational numbers, rational arithmetic, and a summation operator. ; =), is able to express a variety of aggregate functions commonly found in real database query languages. The main results of this paper remain valid in a uniform way if any summation-like primitive, such as bounded product, is added to the language. This approach is more disciplined and general than those proposed by In section 3, we prove that every function f : s ! t expressible in ; =) can be computed without using any intermediate data whose depth of nesting of sets exceeds that of the input and output. This is known as the conservative extension property. Conservativity of nested rela-tional query languages in the absence of aggregate functions was studied by Paredaens and Van Gucht 19] and Wong 26]. The former proved that it holds when input and output are at relations. The latter generalized it to any input and output. Conservativity in the presence of aggregate functions was not previously studied. In section 4, the conservative extension property is used to demonstrate the somewhat surprising fact that NRC ; =) cannot express the usual linear ordering on rational numbers. As linear orders play a central role in fundamental data organization algorithms 14], this calls for special attention. We present a technique for lifting linear order at base types to linear 1 order at all types. This technique yields linear orders that are expressible ; =) with linear orders at base types. Linear order is known to increase expressive power in the context …